﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;

namespace T04WebService
{/// <summary>
 /// user的數據訪問類：增刪改查等操作
 /// </summary>
    namespace T01WPF
    {
        public class UserDao
        {
            //MySQL的連接串描述
            private static string connStr
            = "server=localhost;user=root;password=;database=gametest;sslmode =none;";
            /// <summary>
            /// 登錄檢查
            /// </summary>
            /// <param name="username">用戶名</param>
            /// <param name="password">密碼</param>
            /// <returns>0：無效 1：登錄成功</returns>
            public static int LoginCheck(string username, string password)
            {
                //默認無效的用戶名和密碼
                int number = 0;
                //構建MySQL連接對象
                MySqlConnection conn = new MySqlConnection(connStr);

                try
                {
                    //打開數據庫鏈接
                    conn.Open();
                    //比較用戶名與密碼的sql語句
                    string sql = "SELECT COUNT(id) FROM `tb_users` WHERE `username`='"
                        + username + "' AND `password`='" + password + "'";
                    Debug.WriteLine("SQL語句:" + sql);
                    //創建SQL命令
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    //執行SQL命令
                    object result = cmd.ExecuteScalar();
                    if (result != null)
                    {
                        number = Convert.ToInt32(result);
                    }
                }
                catch (Exception ex)
                {
                    Debug.WriteLine("數據庫訪問異常...");
                    Debug.WriteLine("======================");
                    Debug.WriteLine(ex.ToString());
                    Debug.WriteLine("======================");
                }
                finally
                {
                    if (conn != null)
                    {
                        //關閉數據庫鏈接
                        conn.Close();
                    }
                }
                return number;
            }

            /// <summary>
            /// 獲取所有用戶列表
            /// </summary>
            /// <returns>用戶列表結合</returns>
            public static List<User> LoadData()
            {
                //构建用戶集合
                List<User> userList = new List<User>();

                MySqlConnection conn = new MySqlConnection(connStr);

                try
                {
                    conn.Open();
                    string sql = "SELECT * FROM tb_users";
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    //獲取數據訪問的游標(當前記錄）
                    MySqlDataReader rdr = cmd.ExecuteReader();
                    //依次訪問數據記錄並加入數據集合
                    while (rdr.Read())
                    {
                        User user = new User();
                        user.Id = Convert.ToInt64(rdr["id"]);
                        user.Username = Convert.ToString(rdr["username"]);
                        user.Password = Convert.ToString(rdr["password"]);
                        user.Nickname = Convert.ToString(rdr["nickname"]);
                        user.IsAdmin = Convert.ToInt16(rdr["is_admin"]) == 1 ? true : false;
                        userList.Add(user);
                    }
                    //關閉游標
                    rdr.Close();
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(ex.ToString());
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
                //return new NotFiniteNumberException();
                return userList;
            }

            /// <summary>
            /// 按条件获取用户列表
            /// </summary>
            /// <param name="condition">条件参数值</param>
            /// <returns>符合条件的用户集合</returns>
            public static List<User> LoadData(string condition)
            {
                //构建用戶集合
                List<User> userList = new List<User>();

                MySqlConnection conn = new MySqlConnection(connStr);
                try
                {
                    conn.Open();
                    //模糊查询
                    string sql = "SELECT * FROM tb_users WHERE username LIKE @username OR nickname LIKE @nickname";
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    //安全的参数（占位符）
                    cmd.Parameters.AddWithValue("@username",
                        "%" + condition + "%");
                    MySqlDataReader rdr = cmd.ExecuteReader();
                    cmd.Parameters.AddWithValue("@nickname",
                        "%" + condition + "%");
                    while (rdr.Read())
                    {
                        User user = new User();
                        user.Id = Convert.ToInt64(rdr["id"]);
                        user.Id = Convert.ToInt64(rdr["id"]);
                        user.Username = Convert.ToString(rdr["username"]);
                        user.Password = Convert.ToString(rdr["password"]);
                        user.Nickname = Convert.ToString(rdr["nickname"]);
                        user.IsAdmin = Convert.ToInt16(rdr["is_admin"]) == 1 ? true : false;
                        userList.Add(user);
                    }
                    rdr.Close();

                }
                catch (Exception e)
                {
                    Debug.WriteLine(e.ToString());
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                Debug.WriteLine(userList.Count);
                return userList;
            }

            /// <summary>
            /// 新增用户
            /// </summary>
            /// <param name="user">用户数据实体</param>
            /// <returns>MySQL自增ID</returns>
            public static long Insert(User user)
            {
                long insertId = 0;
                MySqlConnection conn = new MySqlConnection(connStr);
                try
                {
                    conn.Open();
                    string sql = "INSERT INTO tb_users "
                        + "(username,password,nickname,is_admin) "
                        + "VALUES "
                        + "(@username,@password,@nickname,@is_admin) ";
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    //参数绑定
                    cmd.Parameters.AddWithValue("@username", user.Username);
                    cmd.Parameters.AddWithValue("@password", user.Password);
                    cmd.Parameters.AddWithValue("@nickname", user.Nickname);
                    cmd.Parameters.AddWithValue("@is_admin", user.IsAdmin);
                    cmd.ExecuteNonQuery();
                    insertId = cmd.LastInsertedId;
                }
                catch (Exception ex)
                {
                    Debug.WriteLine("数据插入异常: " + ex.ToString());
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                return insertId;
            }

            /// <summary>
            /// 更新用户信息
            /// </summary>
            /// <param name="user">用户数据实体</param>
            public static void Update(User user)
            {
                MySqlConnection conn = new MySqlConnection(connStr);
                try
                {
                    conn.Open();
                    string sql = "UPDATE tb_users SET "
                        + "username=@username, "
                        + "password=@password, "
                        + "nickname=@nickname, "
                        + "is_admin=@is_admin "
                        + "WHERE id=@id";
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    //参数绑定
                    cmd.Parameters.AddWithValue("@username", user.Username);
                    cmd.Parameters.AddWithValue("@password", user.Password);
                    cmd.Parameters.AddWithValue("@nickname", user.Nickname);
                    cmd.Parameters.AddWithValue("@is_admin", user.IsAdmin);
                    cmd.Parameters.AddWithValue("@id", user.Id);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Debug.WriteLine("数据更新异常: " + ex.ToString());
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }


            }

            /// <summary>
            /// 删除用户信息
            /// </summary>
            /// <param name="id">删除用户的内部id</param>
            public static void Delete(long id)
            {
                MySqlConnection conn = new MySqlConnection(connStr);
                try
                {
                    conn.Open();
                    string sql = "DELETE FROM tb_users WHERE id=@id";
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@id", id);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Debug.WriteLine("数据删除错误" + ex.ToString());

                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Clone();
                    }
                }
            }

            public static User GetUserByUsername(string username)
            {
                //构建用戶对象 
                User user = new User();

                MySqlConnection conn = new MySqlConnection(connStr);
                try
                {
                    conn.Open();
                    //模糊查询
                    string sql = "SELECT * FROM tb_users WHERE username = @username";
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    //安全的参数（占位符）
                    cmd.Parameters.AddWithValue("@username", username);
                    MySqlDataReader rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        user.Id = Convert.ToInt64(rdr["id"]);
                        user.Id = Convert.ToInt64(rdr["id"]);
                        user.Username = Convert.ToString(rdr["username"]);
                        user.Password = Convert.ToString(rdr["password"]);
                        user.Nickname = Convert.ToString(rdr["nickname"]);
                        user.IsAdmin = Convert.ToInt16(rdr["is_admin"]) == 1 ? true : false;

                    }
                    rdr.Close();
                }
                catch (Exception e)
                {
                    Debug.WriteLine(e.ToString());
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                return user;
            }
        }
    }
}